package com.sinba.common.user.dao;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.Map;

import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.util.StringUtils;

import com.sinba.common.dao.BaseDAOImplHibernate;
import com.sinba.common.dto.PageDTO;
import com.sinba.common.user.dto.RegisterUserQueryDTO;
import com.sinba.common.user.entity.RegisterUser;

public class RegisterUserDAO extends BaseDAOImplHibernate<RegisterUser> implements IRegisterUserDAO {

	public PageDTO findPager( RegisterUserQueryDTO qdto, String sord, String sidx )
    {
        final DetachedCriteria dc = DetachedCriteria.forClass( RegisterUser.class );
        if ( qdto != null ){
        	if(StringUtils.hasText(qdto.getParentAccount() )){
        		dc.add( Restrictions.eq("parentAccount",qdto.getParentAccount()));
        	}
        	if(StringUtils.hasText(qdto.getLoginName())){
        		dc.add( Restrictions.like("loginName",qdto.getLoginName(),MatchMode.ANYWHERE ) );
        	}
        	if(StringUtils.hasText(qdto.getUserName())){
        		dc.add( Restrictions.like("userName",qdto.getUserName(),MatchMode.ANYWHERE ) );
        	}
        	
        	if(StringUtils.hasText(qdto.getPhone())){
        		dc.add( Restrictions.like("mobile",qdto.getPhone(),MatchMode.ANYWHERE ) );
        	}
        	
        	if(StringUtils.hasText(qdto.getEmail())){
        		dc.add( Restrictions.like("email",qdto.getEmail(),MatchMode.ANYWHERE ) );
        	}
        	
        	if(StringUtils.hasText(qdto.getPhone())){
        		dc.add( Restrictions.like("phone",qdto.getPhone(),MatchMode.ANYWHERE ) );
        	}
        	
        	if(StringUtils.hasText(qdto.getBankAccount())){
        		dc.add( Restrictions.like("bankAccount",qdto.getBankAccount(),MatchMode.ANYWHERE ) );
        	}
        	
        	if(StringUtils.hasText(qdto.getQq())){
        		dc.add( Restrictions.like("qq",qdto.getQq(),MatchMode.ANYWHERE ) );
        	}
        	
        	if(StringUtils.hasText(qdto.getStatus())){
        		dc.add( Restrictions.eq("status",qdto.getStatus()) );
        	}
        	
        	if(StringUtils.hasText(qdto.getRecommendUser())){
        		dc.add( Restrictions.eq("recommendUser",qdto.getRecommendUser()));
        	}
        	
        	if(StringUtils.hasText(qdto.getRegisterLevel())){
        		dc.add( Restrictions.eq("registerLevel",qdto.getRegisterLevel()));
        	}
        	if(StringUtils.hasText(qdto.getRegisterLocation())){
        		dc.add( Restrictions.eq("registerLocation",qdto.getRegisterLocation()));
        	}
        	if(StringUtils.hasText(qdto.getDeclarationCenters())){
        		dc.add( Restrictions.eq("declarationCenters",qdto.getDeclarationCenters()));
        	}
        	
        	if(qdto.getLastOptTime()!=null){
        		dc.add(Restrictions.or(Restrictions.isNull("userStatus"), Restrictions.eq("userStatus", "0")));
        		dc.add(Restrictions.and(Restrictions.isNotNull("lastOptTime"), Restrictions.lt("lastOptTime", new Date())));
        	}
        	
        }
        if(sord!=null&&!sord.equals("")&&sidx!=null&&!sidx.equals("")){
            if(sord.equals("desc"))
            	dc.addOrder(Order.desc(sidx));
            else
            	dc.addOrder(Order.asc(sidx));
        }
        return super.findPageByCriteria(dc, qdto.getStart(), qdto.getLimit());
    }
	
	public List<RegisterUser> userValidate(String loginName,String password,String pwdLevel){
		
		final DetachedCriteria dc = DetachedCriteria.forClass( RegisterUser.class );
//		dc.add(Restrictions.eq("status", "Activation"));
		dc.add(Restrictions.eq("loginName", loginName));
		if("password1".equals(pwdLevel))
			dc.add(Restrictions.eq("password1", password));
		if("password2".equals(pwdLevel))
			dc.add(Restrictions.eq("password2", password));
		if("password3".equals(pwdLevel))
			dc.add(Restrictions.eq("password3", password));
		return super.getHibernateTemplate().findByCriteria(dc);
		
	}
	/**
	 * 分页查询我直推的用户
	 * @param qdto
	 * @param sord
	 * @param sidx
	 * @return
	 */
	public PageDTO findDirectPushUserPager(RegisterUserQueryDTO qdto, String sord, String sidx ){
		final DetachedCriteria dc = DetachedCriteria.forClass( RegisterUser.class );
		if(qdto!=null && qdto.getLoginName()!=null)
			dc.add(Restrictions.eq("recommendUser", qdto.getLoginName()));
		else
			dc.add(Restrictions.eq("recommendUser","not"));
		
		if(sord!=null&&!sord.equals("")&&sidx!=null&&!sidx.equals("")){
            if(sord.equals("desc"))
            	dc.addOrder(Order.desc(sidx));
            else
            	dc.addOrder(Order.asc(sidx));
        }
		return super.findPageByCriteria(dc,qdto.getStart(), qdto.getLimit());
	}
	/**
	 * 查询指定接点人和位置的用户统计
	 * @param contactNo
	 * @param area
	 * @return
	 */
	public boolean findAreaRegUser(String contactNo,String area){
		final DetachedCriteria dc = DetachedCriteria.forClass( RegisterUser.class );
		dc.add(Restrictions.eq("contactNo", contactNo));
		dc.add(Restrictions.eq("registerLocation", area));
		//dc.add(Restrictions.eq("status", "Activation"));
		Integer  stat = super.statCriteria(dc);
		if(stat>0)
			return true;
		else
			return false;
	}
	
	/**
	 * 查询指定接点人和位置的用户统计
	 * @param contactNo
	 * @param area
	 * @return
	 */
	public RegisterUser findAreaRegUser1(String contactNo,String area){
		final DetachedCriteria dc = DetachedCriteria.forClass( RegisterUser.class );
		dc.add(Restrictions.eq("contactNo", contactNo));
		dc.add(Restrictions.eq("registerLocation", area));
		List<RegisterUser>  users  =super.getHibernateTemplate().findByCriteria(dc);
		if(users.size()>0){
			return users.get(0);
		}else{
			return null;
		}
	}
	/**
	 * 查询前一天可计划财富见点佣金的会员
	 * @return
	 */
	public List<RegisterUser> findLookPointAwardUser(){
		final DetachedCriteria dc = DetachedCriteria.forClass( RegisterUser.class );
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Calendar ca = Calendar.getInstance();
		ca.setTime(new Date());
		ca.set(Calendar.DATE, ca.get(Calendar.DATE)-1);
		Date start = null;
		Date end = null;
		try {
			start = sdf.parse(sdf.format(ca.getTime())+" 00:00:00");
			end = sdf.parse(sdf.format(ca.getTime())+" 23:59:59");
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		//当前时间的前一天
//		dc.add(Restrictions.between("activationTime", start,end));
		
//		dc.add(Restrictions.ne("grantLookPointAward", "YES"));
		
		//状态为激活的
		dc.add(Restrictions.eq("status", "Activation"));
		
		//为C区的
		dc.add(Restrictions.eq("registerLocation", "cArea"));
		
		return super.getHibernateTemplate().findByCriteria(dc);
	}
	
	public List<RegisterUser> lastActiveUser(Date startTime){
		final DetachedCriteria dc = DetachedCriteria.forClass( RegisterUser.class );
		dc.add(Restrictions.eq("status", "Activation"));
		dc.add(Restrictions.gt("activationTime",startTime));
		dc.addOrder(Order.asc("activationTime"));
		PageDTO p = super.findPageByCriteria(dc, 0, 3);
		for(RegisterUser ru : (List<RegisterUser>)p.getData()){
			System.out.println("激活时间:"+ru.getActivationTime()+",会员："+ru.getLoginName());
		}
		return p.getData();
	}
	
	//查询的链接用户
	public List<RegisterUser> findLinkUser(String linkCode){
		final DetachedCriteria dc = DetachedCriteria.forClass( RegisterUser.class );
		dc.add(Restrictions.like("linkcode", linkCode, MatchMode.START));
		return super.getHibernateTemplate().findByCriteria(dc);
	}
	//统计链接下的用户总数
	public Integer countLinkCode(String linkCode,String status){
		final DetachedCriteria dc = DetachedCriteria.forClass( RegisterUser.class );
		if(status!=null){
			dc.add(Restrictions.eq("status", "Activation"));
		}
		dc.add(Restrictions.like("linkcode", linkCode, MatchMode.START));
		return super.statCriteria(dc);
	}
	
	
	//符合极差的会员有
	public List<RegisterUser> findJiCha(String linkCode,String[] notInUser){
		final DetachedCriteria dc = DetachedCriteria.forClass( RegisterUser.class );
		//已激活
		dc.add(Restrictions.eq("status", "Activation"));
		//完成9个直推的会员
		dc.add(Restrictions.ge("recommendQuantity", 9));
		//查一条线的用户
		dc.add(Restrictions.sqlRestriction("'"+linkCode+"' like linkcode+'%'"));
	
		//排除用户
		if(notInUser!=null && notInUser.length>0){
			dc.add(Restrictions.not(Restrictions.in("loginName", notInUser)));
		}
		dc.addOrder(Order.desc("registerUserId"));
		
		return super.getHibernateTemplate().findByCriteria(dc);
	}
	//符合极差的会员有(领导号)
	public List<RegisterUser> findJiChaLeader(String linkCode,String[] leaderUser){
		final DetachedCriteria dc = DetachedCriteria.forClass( RegisterUser.class );
		//查一条线的用户
		dc.add(Restrictions.sqlRestriction("'"+linkCode+"' like linkcode+'%'"));
		dc.add(Restrictions.in("loginName", leaderUser));
		dc.addOrder(Order.desc("registerUserId"));
		return super.getHibernateTemplate().findByCriteria(dc);
	}
	//我的团队
	public PageDTO myTeamPager(RegisterUserQueryDTO qdto, String sord, String sidx){
		final DetachedCriteria dc = DetachedCriteria.forClass( RegisterUser.class );
		dc.add(Restrictions.like("linkcode", qdto.getLinkCode(),MatchMode.START));
		if(qdto!=null){
			if(StringUtils.hasText(qdto.getUserName())){
				dc.add(Restrictions.like("userName", qdto.getUserName(), MatchMode.ANYWHERE));
			}
			
			if(StringUtils.hasText(qdto.getLoginName())){
				dc.add(Restrictions.like("loginName", qdto.getLoginName(), MatchMode.ANYWHERE));
			}
		}
		if(sord!=null&&!sord.equals("")&&sidx!=null&&!sidx.equals("")){
            if(sord.equals("desc"))
            	dc.addOrder(Order.desc(sidx));
            else
            	dc.addOrder(Order.asc(sidx));
        }
		return super.findPageByCriteria(dc, qdto.getStart(), qdto.getLimit());
	}
	
	//今天推荐人数(TodayRec)
	//今天推荐并激活人数(TodayRecActivation)
	//今天注册会员数(TodayRegSum)
	//今天激活会员数(TodayActivationSum)
	//推荐全部人数(AllRec)
	//推荐已激活人数(AllRecActivation)
	//我的团队总数(Team)
	//我的团队激活人数(TeamActivation)
	//运营中心已激活(DeclarationCenterActivation)
	public Integer userCount(String loginName,String type,String[] notInUser){
		final DetachedCriteria dc = DetachedCriteria.forClass( RegisterUser.class );
		//推荐条件
		if("TodayRec".equals(type) || "TodayRecActivation".equals(type) || "AllRec".equals(type) || "AllRecActivation".equals(type)){
			dc.add(Restrictions.eq("recommendUser", loginName));
		}
		//当天条件
		if("TodayRec".equals(type) || "TodayRecActivation".equals(type) || "TodayRegSum".equals(type)){
			Calendar timeStartCl = new GregorianCalendar();
			timeStartCl.setTime(new Date());
			timeStartCl.set(Calendar.HOUR_OF_DAY, 0);
			timeStartCl.set(Calendar.MINUTE, 0);
			timeStartCl.set(Calendar.SECOND, 0);
	    	
			Calendar timeEndCl = new GregorianCalendar();
			timeEndCl.setTime(new Date());
			timeEndCl.set(Calendar.HOUR_OF_DAY, 23);
			timeEndCl.set(Calendar.MINUTE, 59);
			timeEndCl.set(Calendar.SECOND, 59);
			
			dc.add(Restrictions.between("registerTime", timeStartCl.getTime(), timeEndCl.getTime()));
		}
		
		//当天激活条件
		if("TodayActivationSum".equals(type)){
			Calendar timeStartCl = new GregorianCalendar();
			timeStartCl.setTime(new Date());
			timeStartCl.set(Calendar.HOUR_OF_DAY, 0);
			timeStartCl.set(Calendar.MINUTE, 0);
			timeStartCl.set(Calendar.SECOND, 0);
	    	
			Calendar timeEndCl = new GregorianCalendar();
			timeEndCl.setTime(new Date());
			timeEndCl.set(Calendar.HOUR_OF_DAY, 23);
			timeEndCl.set(Calendar.MINUTE, 59);
			timeEndCl.set(Calendar.SECOND, 59);
			
			dc.add(Restrictions.between("activationTime", timeStartCl.getTime(), timeEndCl.getTime()));
		}
		
		//激活状态
		if("TodayRecActivation".equals(type) || "AllRecActivation".equals(type) || "TeamActivation".equals(type) || "DeclarationCenterActivation".equals(type)){
			dc.add(Restrictions.eq("status", "Activation"));
		}
		
		//我的团队
		if("Team".equals(type) || "TeamActivation".equals(type)){
			RegisterUser user = super.findUniqueBy("loginName", loginName);
			dc.add(Restrictions.like("linkcode", user.getLinkcode(), MatchMode.START));
		}
		//运营中心
		if("DeclarationCenterActivation".equals(type)){
			dc.add(Restrictions.eq("declarationCenters", loginName));
		}
		
		//排除用户
		if(notInUser!=null && notInUser.length>0){
			dc.add(Restrictions.not(Restrictions.in("loginName", notInUser)));
		}
		
		return super.statCriteria(dc);
	}
	
	/**
	 * 查出我团队中推荐大于9的会员及会员团队数
	 * @param loginName
	 */
	public List<Object[]> teamNumber(String loginName,String notInLoginName){
		String hql = "select ru2.loginName,(select count(*) from RegisterUser ru1 where ru1.linkcode like ru2.linkcode+'%' and status='Activation') "+
		"from RegisterUser ru2 where loginName in (select ru3.loginName from RegisterUser ru3 where ru3.recommendUser='"+loginName+"'  and status='Activation' and recommendQuantity>0)";
		//System.out.println("::::::::::::::::::::::::::::::::::::::::::::");
		//System.out.println(hql);
		//System.out.println("::::::::::::::::::::::::::::::::::::::::::::");
		return super.getHibernateTemplate().find(hql);
	}
	
	/**
	 * 要统计的用户
	 * @param queryDTO
	 * @return
	 */
	public List<RegisterUser> findRegUser(RegisterUserQueryDTO queryDTO){
		final DetachedCriteria dc = DetachedCriteria.forClass( RegisterUser.class );
		//已激活
		dc.add(Restrictions.eq("status", "Activation"));
		//完成9个直推的会员
		dc.add(Restrictions.ge("recommendQuantity", 1));
		
		if(queryDTO!=null){
			//我的团队
			if(StringUtils.hasText(queryDTO.getLinkCode())){
				dc.add(Restrictions.like("linkcode", queryDTO.getLinkCode(),MatchMode.START));
			}
			
			//我推荐
			if(StringUtils.hasText(queryDTO.getRecommendUser())){
				dc.add(Restrictions.like("recommendUser", queryDTO.getRecommendUser(),MatchMode.START));
			}
		}
		dc.addOrder(Order.desc("registerUserId"));
		return super.getHibernateTemplate().findByCriteria(dc);
		
	}
	
	//反向取一条线的会员
	public List<RegisterUser> getReverseLineUser(String linkCode,String[] leaderUser){
		final DetachedCriteria dc = DetachedCriteria.forClass( RegisterUser.class );
		//查一条线的用户
		dc.add(Restrictions.sqlRestriction("'"+linkCode+"' like linkcode+'%'"));
		if(leaderUser!=null){
			dc.add(Restrictions.in("loginName", leaderUser));
		}
		dc.addOrder(Order.desc("registerUserId"));
		return super.getHibernateTemplate().findByCriteria(dc);
	}
	
	/**
	 * 判断是否是我团队
	 * @param linkCode
	 * @param loginName
	 * @return
	 */
	public boolean isMyTeam(String linkCode,String loginName){
		final DetachedCriteria dc = DetachedCriteria.forClass( RegisterUser.class );
		dc.add(Restrictions.like("linkcode", linkCode,MatchMode.START));
		dc.add(Restrictions.eq("loginName", loginName));
		Integer sum = super.statCriteria(dc);
		if(sum>0){
			return true;
		}else{
			return false;
		}
	}
	
	/**
	 * 根据接点人查找
	 * @param users
	 * @return user
	 */
	
	public List<RegisterUser> findContactUser(String[] loginNames){
		final DetachedCriteria dc = DetachedCriteria.forClass( RegisterUser.class );
		//dc.add(Restrictions.eq("status", "Activation"));
		dc.add(Restrictions.in("contactNo", loginNames));
		dc.addOrder(Order.asc("registerTime"));
		//dc.add(Restrictions.eq("registerLocation", registerLocation));
		
		return super.getHibernateTemplate().findByCriteria(dc);
	}
	
	
	/**
	 * 根据接点人查找
	 * @param users
	 * @return user
	 */
	
	public RegisterUser findAearContactUser(String loginName , String registerLocation){
		final DetachedCriteria dc = DetachedCriteria.forClass( RegisterUser.class );
		//dc.add(Restrictions.eq("status", "Activation"));
		dc.add(Restrictions.eq("contactNo", loginName));
		dc.add(Restrictions.eq("registerLocation", registerLocation));
		dc.addOrder(Order.asc("registerTime"));
		//dc.add(Restrictions.eq("registerLocation", registerLocation));
		List<RegisterUser> users = super.getHibernateTemplate().findByCriteria(dc);
		if(users.size()>0){
			return users.get(0);
		}else{
			return null;
		}
	}

	/**
	 * 查询已激活会员
	 * @return
	 */
	public List<RegisterUser> findActivationUser() {
		// TODO Auto-generated method stub
		final DetachedCriteria dc = DetachedCriteria.forClass( RegisterUser.class );
		dc.add(Restrictions.eq("status", "Activation"));
		dc.add(Restrictions.ne("registerLevel", "L6"));
		dc.add(Restrictions.ne("userStatus", "1"));
		return super.getHibernateTemplate().findByCriteria(dc);
	}
	
	/**
	 * 判断我推荐的会员有没有在左区
	 * @param loginName
	 * @return 判断结果
	 */
	public boolean myRecommedLeftArea(String loginName){
		final DetachedCriteria dc = DetachedCriteria.forClass( RegisterUser.class );
		dc.add(Restrictions.eq("status", "Activation"));
		dc.add(Restrictions.eq("recommendUser", loginName));
		dc.add(Restrictions.eq("registerLocation", "aArea"));
		List<RegisterUser> list = super.getHibernateTemplate().findByCriteria(dc);
		if(list.size()>0){
			return true;
		}else{
			return false;
		}
	}
	
	/**
	 * 根据LinkCode查线最长的会员
	 * @param linkCode
	 * @return
	 */
	public List<RegisterUser> findJiDianRen(String linkCode){
		String hql = "from RegisterUser where level =(select max(level) from RegisterUser where linkCode like '"+linkCode+"%') and linkCode like '"+linkCode+"%'";
		//System.out.println(hql);
		return super.getHibernateTemplate().find(hql);
	}
	
	/**
	 * 判断左区是否有激活会员
	 * @param linkCode
	 * @return
	 */
	public boolean isLeftActiveUser(String linkCode){
		final DetachedCriteria dc = DetachedCriteria.forClass( RegisterUser.class );
		dc.add(Restrictions.like("linkcode", linkCode,MatchMode.START));
		dc.add(Restrictions.eq("status", "Activation"));
		Integer sum = super.statCriteria(dc);
		if(sum>0){
			return true;
		}else{
			return false;
		}
	}
	
	public Map<String, RegisterUser> findUserByLoginNames( String[] loginNames ){
		final DetachedCriteria dc = DetachedCriteria.forClass( RegisterUser.class );
		dc.add(Restrictions.in("loginName", loginNames));
		List<RegisterUser> registerUsers = super.getHibernateTemplate().findByCriteria(dc);
		Map<String, RegisterUser> result = new java.util.HashMap<String, RegisterUser>();
		if ( registerUsers != null && registerUsers.size() > 0) {
			for (RegisterUser registerUser : registerUsers) {
				result.put(registerUser.getLoginName(), registerUser);
			}
		}
		return result;
	}
	
	
	/**
	 * 查出超时要删除的用户
	 * @param status
	 * @return
	 */
	public List<RegisterUser> findsTimeOutUser(){
		String hql = "from RegisterUser where (status='unfinished' and hour(timediff(now(),createTime)) > 12) or (status='NotActivation' and hour(timediff(now(),createTime)) > 24)";
		return super.getHibernateTemplate().find(hql);
	}
	
	
	/**
	 * 我推荐的所有激活人员的投资金额
	 * @param loginName
	 */
	public Long myRecommedMoney(String loginName){
		String hql = "select sum(registerMoney) from RegisterUser where status ='Activation' and recommendUser='"+loginName+"'";
		Long result = 0L;
		try {
			List<Object> objs = super.getHibernateTemplate().find(hql);
			for(Object obj : objs){
				result = (Long)obj;
			}
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println("我推荐的所有激活人员的投资金额统计出错");
		}
		if(result==null){
			result = 0L;
		}
		return result;
	}
	
}
